IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GXBZ_EMR_ZYYZ]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[USP_GXBZ_EMR_ZYYZ]
GO
 
/**********
[版本号]1.0.0
[创建时间]2018-12-13
[作者]SJ
[版权]卫宁健康科技集团股份有限公司
[描述] 
[功能说明]重庆cdc数据上传--住院医嘱GXBZ_EMR_ZYYZ数据上传
[返回值]
[调用的USP]
[调用实例] EXEC USP_GXBZ_EMR_ZYYZ
[修改历史]
**********/
CREATE PROC [dbo].[USP_GXBZ_EMR_ZYYZ]    
AS 
  SET XACT_ABORT ON 
DECLARE 
	@kssj datetime,					--上传开始时间
	@jssj datetime					--上传结束时间
BEGIN                                                                                                  

	
	
	IF NOT EXISTS(SELECT table_time FROM SYS_SJSC_TABLE 
	WHERE TABLE_NAME = 'GXBZ_EMR_ZYYZ')
	BEGIN 
		INSERT INTO SYS_SJSC_TABLE(TABLE_NAME,TABLE_TIME)
		VALUES('GXBZ_EMR_ZYYZ','2015-07-01')
	END 
	
	--获取已上传的时间
	SELECT @kssj =TABLE_TIME  FROM SYS_SJSC_TABLE(NOLOCK)
	WHERE TABLE_NAME = 'GXBZ_EMR_ZYYZ'
	
	--获取源表中最大上传时间
	SELECT @jssj = MAX(TBRQ) FROM MZYZMXB(NOLOCK)
	
	IF(@jssj>ISNULL(@kssj,'1900-01-01'))
	BEGIN
		BEGIN TRY
		BEGIN TRAN

	-------创建临时表  
	CREATE TABLE #TEMP_GXBZ_EMR_ZYYZ(  
		JZLSH                varchar(64)          not null,
		XGBZ                 varchar(1)           not null,
		GRJBXXBSH            varchar(64)          not null,
		CXJMJKDABH           varchar(17)          not null,
		YLJGDM               varchar(22)          null,
		YLJGMC               varchar(70)          null,
		CJSJ                 datetime             null,
		XDRGH                varchar(64)          null,
		XDRXM                varchar(30)          null,
		WDGLJGDM             varchar(22)          null,
		WDGLJGMC             varchar(70)          null,
		JYSJ                 datetime             null,
		BCHDM                varchar(20)          null,
		BCHMC                varchar(100)         null,
		BFHDM                varchar(20)          null,
		BFHMC                varchar(100)         null,
		BQHDM                varchar(20)          null,
		BQHMC                varchar(100)         null,
		ZXKSBM               varchar(64)          null,
		ZXKSMC               varchar(32)          null,
		TZ                   decimal(6,2)         null,
		YZLBDM               varchar(50)          null,
		YZLBMC               varchar(100)         null,
		YZXMLXDM             varchar(50)          null,
		YZXMLXMC             varchar(100)         null,
		YZZXSJ               datetime             null,
		YZZZSJ               datetime             null,
		YZXMNRMS             varchar(1024)        null,
		YZXDSJ               datetime             null,
		XDKSBM               varchar(64)          null,
		XDKSMC               varchar(32)          null,
		SHSJ                 datetime             null,
		SHRGH                varchar(64)          null,
		SHRXM                varchar(30)          null,
		HDSJ                 datetime             null,
		HDHSGH               varchar(64)          null,
		HDHSXM               varchar(30)          null,
		TZYZZGH              varchar(64)          null,
		TZYZZXM              varchar(30)          null,
		YZQXSJ               datetime             null,
		YZQXZGH              varchar(64)          null,
		YZQXZXM              varchar(30)          null,
		YZBZ                 varchar(1024)        null,
		YZZXZT               varchar(50)          null,
		ZXRGH                varchar(64)          null,
		ZXRXM                varchar(30)          null,
		DZSQDBH              varchar(100)         null,
		CFYPZH               varchar(64)          null,
		MJ                   varchar(16)          not null,
		TBRQ                 datetime             not null,
		SCZT                 varchar(1)           not null,
		CS_JYBZ              int                  not null,
		SCSJ                 datetime             not null
	) ON [PRIMARY]

	--插业务数据到临时表
	INSERT INTO #TEMP_GXBZ_EMR_ZYYZ (
					JZLSH,
					XGBZ,
					GRJBXXBSH,
					CXJMJKDABH,
					YLJGDM,
					YLJGMC,
					CJSJ,
					XDRGH,
					XDRXM,
					WDGLJGDM,
					WDGLJGMC,
					JYSJ,
					BCHDM,
					BCHMC,
					BFHDM,
					BFHMC,
					BQHDM,
					BQHMC,
					ZXKSBM,
					ZXKSMC,
					TZ,
					YZLBDM,
					YZLBMC,
					YZXMLXDM,
					YZXMLXMC,
					YZZXSJ,
					YZZZSJ,
					YZXMNRMS,
					YZXDSJ,
					XDKSBM,
					XDKSMC,
					SHSJ,
					SHRGH,
					SHRXM,
					HDSJ,
					HDHSGH,
					HDHSXM,
					TZYZZGH,
					TZYZZXM,
					YZQXSJ,
					YZQXZGH,
					YZQXZXM,
					YZBZ,
					YZZXZT,
					ZXRGH,
					ZXRXM,
					DZSQDBH,
					CFYPZH,
					MJ,
					TBRQ,
					SCZT,
					CS_JYBZ,
					SCSJ
					) 
	SELECT
		A.YZID JZLSH, 
		A.XGBZ AS XGBZ,
		(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) AS GRJBXXBSH,
		(select top 1 JBXX.CXJMJKDABH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) AS CXJMJKDABH, 
		ISNULL((SELECT SPTDM FROM SYS_ZCJG_DZ where A.YLJGDM=QPTDM),'500103000000001') AS YLJGDM,
		ISNULL((SELECT JGMC FROM SYS_ZCJG_DZ where A.YLJGDM=QPTDM),'500103000000001') AS YLJGMC, 
		A.TBRQ AS CJSJ, 
		A.XDRGH AS XDRGH,
		A.XDRXM AS XDRXM, 
		'500103000000001' AS WDGLJGDM,
		'渝中区卫计委' AS WDGLJGMC,
		C.RYSJ AS JYSJ,
		'' AS BCHDM,	--病床号
		'' AS BCHMC,	--病床名称
		'' AS BFHDM,	--病房号
		'' AS BFHMC,	--病房名称
		'' AS BQHDM,	--病区号代码
		'' AS BQHMC,	--病区号名称
		A.ZXKSBM AS ZXKSBM,	--执行科室代码
		A.ZXKSMC AS ZXKSMC,	--执行科室名称
		null AS TZ,	--体重
		A.YZLB AS YZLBDM,	--医嘱类别代码
		CASE A.YZLB WHEN '1' THEN '长期（在院）' WHEN '2' THEN '临时（在院）' WHEN '3' THEN '出院带药' ELSE '其他' END AS YZLBMC,	--医嘱类别名称
		A.YZXMLXDM AS YZXMLXDM,	--医嘱项目类型代码
		A.YZXMLXMC AS YZXMLXMC,	--医嘱项目名称
		A.YZZXSJ AS YZZXSJ,		--医嘱开始时间
		A.YZZZSJ AS YZZZSJ,		--医嘱终止时间
		A.YZSM AS YZXMNRMS,		--医嘱项目内容描述
		A.YZXDSJ AS YZXDSJ,		--医嘱下达时间
		A.XDKSBM AS XDKSBM,		--下达科室编码
		A.XDKSMC AS XDKSMC,		--下达科室名称
		A.SHSJ AS SHSJ,			--医嘱审核日期
		A.SHRGH AS SHRGH,		--医嘱审核人工号
		A.SHRXM AS SHRXM,		--医嘱审核人姓名
		A.HDSJ AS HDSJ,			--医嘱核对日期
		A.HDHSGH AS HDHSGH,		--核对护士工号
		A.HDHSXM AS HDHSXM,		--核对护士姓名
		A.TZYZZGH AS TZYZZGH,	--停止医嘱者工号
		A.TZYZZXM AS TZYZZXM,	--停止医嘱者姓名
		A.YZZZSJ AS YZQXSJ,		--医嘱取消日期时间
		A.TZYZZGH AS YZQXZGH,	--医嘱取消者工号
		A.TZYZZXM AS YZQXZXM,	--医嘱取消者姓名
		A.YZSM AS YZBZ,			--医嘱备注
		A.YZZXZT AS YZZXZT,		--医嘱执行状态
		A.ZXRGH AS ZXRGH,		--医嘱执行人工号
		A.ZXRXM AS ZXRXM,		--医嘱执行人姓名
		A.DZSQDBH AS DZSQDBH,	--电子申请单编号
		'' AS CFYPZH,			--处方药品组号 
		A.MJ AS MJ,
		A.TBRQ AS TBRQ,
		'0' AS SCZT,
		0 AS CS_JYBZ,
		GETDATE() AS SCSJ
	FROM ZYYZMXB A(NOLOCK)
	LEFT JOIN ZYJZJLB C(NOLOCK) ON A.YLJGDM=C.YLJGDM AND A.JZLSH=C.JZLSH  
	WHERE a.TBRQ>ISNULL(@kssj,'1900-01-01')
	and 
	(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = C.ZJHM) is not NULL
	
	
	--删除中间库中重复数据
	DELETE A FROM GXBZ_EMR_ZYYZ A,#TEMP_GXBZ_EMR_ZYYZ B
	WHERE A.GRJBXXBSH=B.GRJBXXBSH AND A.YLJGDM=B.YLJGDM AND A.JZLSH=B.JZLSH

	--插入数据至中间库
	INSERT INTO GXBZ_EMR_ZYYZ (JZLSH,XGBZ,GRJBXXBSH,CXJMJKDABH,YLJGDM,YLJGMC,CJSJ,XDRGH,XDRXM,WDGLJGDM,WDGLJGMC,JYSJ,BCHDM,BCHMC,
	BFHDM,BFHMC,BQHDM,BQHMC,ZXKSBM,ZXKSMC,TZ,YZLBDM,YZLBMC,YZXMLXDM,YZXMLXMC,YZZXSJ,YZZZSJ,YZXMNRMS,YZXDSJ,XDKSBM,XDKSMC,SHSJ,SHRGH,
	SHRXM,HDSJ,HDHSGH,HDHSXM,TZYZZGH,TZYZZXM,YZQXSJ,YZQXZGH,YZQXZXM,YZBZ,YZZXZT,ZXRGH,ZXRXM,DZSQDBH,CFYPZH,MJ,TBRQ,SCZT,CS_JYBZ,SCSJ)    
	SELECT JZLSH,XGBZ,GRJBXXBSH,CXJMJKDABH,YLJGDM,YLJGMC,CJSJ,XDRGH,XDRXM,WDGLJGDM,WDGLJGMC,JYSJ,BCHDM,BCHMC,BFHDM,BFHMC,BQHDM,BQHMC,
		ZXKSBM,ZXKSMC,TZ,YZLBDM,YZLBMC,YZXMLXDM,YZXMLXMC,YZZXSJ,YZZZSJ,YZXMNRMS,YZXDSJ,XDKSBM,XDKSMC,SHSJ,SHRGH,SHRXM,HDSJ,HDHSGH,HDHSXM,
		TZYZZGH,TZYZZXM,YZQXSJ,YZQXZGH,YZQXZXM,YZBZ,YZZXZT,ZXRGH,ZXRXM,DZSQDBH,CFYPZH,MJ,TBRQ,SCZT,CS_JYBZ,SCSJ
	FROM #TEMP_GXBZ_EMR_ZYYZ

	COMMIT TRAN

	   SELECT 'T' AS SRESULT, '表：GXBZ_EMR_ZYYZ数据抽取成功！' AS SHINT
   
	   INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME) 
	   VALUES('表：GXBZ_EMR_ZYYZ数据抽取成功','GXBZ_EMR_ZYYZ',GETDATE(),'T','USP_WD_GXBZ_EMR_ZYYZ')  
   
	 -- INSERT INTO SYS_SJSC_TABLENAME(TABLE_NAME, SCKSSJ, SCJSSJ, BZ)
	   --VALUES('GXBZ_EMR_ZYYZ',@sKssj,@sJssj,'')
		--更新上传时间
		UPDATE SYS_SJSC_TABLE SET TABLE_TIME = @jssj WHERE TABLE_NAME = 'GXBZ_EMR_ZYYZ'
		
		

	END TRY BEGIN CATCH
		SELECT 'F' AS SRESULT, '表：GXBZ_EMR_ZYYZ数据抽取失败！' AS SHINT, ERROR_MESSAGE() AS MSG
		ROLLBACK TRAN

		/*记录每步执行的记录日志*/
		INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME,MSG,ERRORCODE) 
		VALUES('表：GXBZ_EMR_ZYYZ数据抽取失败','GXBZ_EMR_ZYYZ',GETDATE(),'F','USP_GXBZ_EMR_ZYYZ',ERROR_MESSAGE(),ERROR_NUMBER())
	END CATCH
	END
END

